﻿using Model;
using System.Data;
using System.Text;

namespace BLL
{
    public class Qs_HeadBLL : BaseBLL
    {
        public int InsertModel(Qs_Head model, string operName)
        {
            string sql = string.Format(@"INSERT INTO  Qs_Head VALUES(" + model.CMY_ID + ",'" + model.USER_ID + "','" + model.FIN_DIRECTOR + "'," + model.OPER_PEOPLE + ")");
            int count = NoQuery(sql, operName);
            return count;
        }

        public int UpdateModel(Qs_Head model, string operName)
        {
            string sql = string.Format(@"UPDATE Qs_Head h SET h.USER_ID='" + model.USER_ID + "', h.FIN_DIRECTOR='" + model.FIN_DIRECTOR + "',h.OPER_PEOPLE=" + model.OPER_PEOPLE + " WHERE h.CMY_ID=" + model.CMY_ID);
            int count = NoQuery(sql, operName);
            return count;
        }

        public DataTable GetModelById(string comId)
        {
            string sql = string.Format(@"SELECT
    t.CMY_ID,
    co.CMY_NAME,
    t.USER_ID,
    t.FIN_DIRECTOR,
    t.OPER_PEOPLE,
    u.USER_NAME
FROM
    Qs_Head t,
    Sys_Company co,
    Sys_User u
WHERE
    t.CMY_ID = co.CMY_ID
    AND t.OPER_PEOPLE = u.USER_ID
    AND t.CMY_ID =" + comId);

            DataTable dt = Query(sql);

            return dt;
        }

        public int GetRecordCount(string whereStr)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT count(1)
FROM
    THKQS.Qs_Head t,
    THKQS.Sys_Company co,
    THKQS.Sys_User u
WHERE
    t.CMY_ID = co.CMY_ID
    AND t.OPER_PEOPLE = u.USER_ID ");
            if (!string.IsNullOrEmpty(whereStr))
            {
                strSql.Append(whereStr);
            }
            return GetCount(strSql.ToString());
        }

        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY t." + orderby + @") as r,
    t.CMY_ID,
    co.CMY_NAME,
    t.USER_ID,
    t.FIN_DIRECTOR,
    t.OPER_PEOPLE,
    u.USER_NAME
FROM
    THKQS.Qs_Head t,
    THKQS.Sys_Company co,
    THKQS.Sys_User u
WHERE
    t.CMY_ID = co.CMY_ID
    AND t.OPER_PEOPLE = u.USER_ID ");
            if (!string.IsNullOrEmpty(whereStr.Trim()))
            {
                strSql.Append(whereStr);
            }
            strSql.Append(@" ) TT");
            if (startIndex >= 2)
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1}+1 AND {0}*{1}", startIndex, endIndex);
            }
            else
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1} AND {0}*{1}", startIndex, endIndex);
            }
            return Query(strSql.ToString());
        }
    }
}